import datetime
import sqlite3
import sys
import os
from pathlib import Path
import threading
if getattr(sys, 'frozen', False):
    # 如果是打包后的程序
    exe_dir = os.path.dirname(sys.executable)
else:
    # 如果是开发环境
    exe_dir = os.path.dirname(os.path.abspath(__file__))
data_path = os.path.join(exe_dir, 'data.db')
# 连接到SQLite数据库
connection = sqlite3.connect(data_path,check_same_thread=False)
# 设置 row_factory 为 sqlite3.Row
connection.row_factory = sqlite3.Row
# 创建一个游标对象
cursor = connection.cursor()
# sqlite3.Row objec 转为字典
def row_to_dict(results):
   list = []
   for row in results:
    obj = {}
    for column in row.keys():
        obj[column] = row[column]
    list.append(obj)
   return list


# 检查表是否存在
cursor.execute("SELECT * FROM sqlite_master WHERE type='table' AND name=?", ('commService',))
table_exists = cursor.fetchone()

if not table_exists:
    # 检查一个表就够了
    # 表不存在，创建表
    # status 0 平常状态 1 待执行 2 执行中 3 执行完成
    create_table_query = f"""
    CREATE TABLE commService (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        host TEXT,
        port TEXT,
        username TEXT,
        password TEXT,
        remotePath TEXT,
        remoteBackupPath TEXT,
        backMaxNum TEXT,
        localPath TEXT
    );
    CREATE TABLE jobList (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        dir TEXT,
        buildCommond TEXT,
        otherCommond TEXT,
        host TEXT,
        port TEXT,
        username TEXT,
        password TEXT,
        remotePath TEXT,
        remoteBackupPath TEXT,
        remoteCommand TEXT,
        backMaxNum TEXT,
        localPath TEXT,
        status TEXT DEFAULT '0'
    );
    
    CREATE TABLE jobLog (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        jobId INTEGER,
        log TEXT,
        status TEXT DEFAULT '1',
        createAt TEXT
    );
    """
    cursor.executescript(create_table_query)
    cursor.execute("INSERT INTO commService VALUES(Null,'','22','','','','','10','' )")
    cursor.execute("UPDATE commService SET host='192.168.31.203',port='22',username='root',password='password',remotePath='/home/html',remoteBackupPath='/home/htmlBackup',backMaxNum='10',localPath='dist'")
    cursor.execute("select * from commService")
    print(row_to_dict(cursor.fetchall()))

    cursor.execute(r"INSERT INTO jobList VALUES(Null, '测试用例','C:\Users\95777\Desktop\py-demo\html','npm run build','node -v','www.limeichao.cn','22','root','这是服务器密码','/home/test/web','/home/test/web-backup','','10','dist','0' )")
    cursor.execute("select * from jobList")
    print(row_to_dict(cursor.fetchall()))

    connection.commit()
    cursor.close()
    print(f"表 已创建。")
else:
    print(f"表 已存在，跳过创建。")
    # 把所有任务重置为状态0
    cursor = connection.cursor()
    cursor.execute("UPDATE jobList SET status='0'")
    connection.commit()
    cursor.close()
    print("所有任务重置为状态0")



# cursor.execute("INSERT INTO jobLog (jobId,result,desc) VALUES(1,'成功','描述')")
# cursor.execute("select * from jobLog")
# print(row_to_dict(cursor.fetchall()))
# cursor.execute("select * from jobLog left join jobList on jobLog.jobId=jobList.id")
# print(row_to_dict(cursor.fetchall()))
# 提交事务
# connection.commit()

class Db:
    def __init__(self):
        self.lock=threading.Lock() 
    def success(self,payload):
        data = {
            "data":payload,
            "code":200,
            "msg":"成功"
        }
        return data
    def error(self,msg):
        data = {
            "data":None,
            "code":500,
            "msg":msg
        }
        return data
        
    # !1
    # 获取公共配置
    def getCommService(self):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            cursor.execute("select * from commService")
            return self.success(row_to_dict(cursor.fetchall())[0])
        except Exception as e:
            print(f"获取公共配置时出现错误: {e}")
            return self.error(f"获取公共配置时出现错误: {e}")
        finally:
            cursor.close()
            self.lock.release()
    # 修改公共配置
    def updateCommService(self , params):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            cursor.execute("update commService set host=?,port=?,username=?,password=?,remotePath=?,remoteBackupPath=?,backMaxNum=?,localPath=?",(params['host'],params['port'],params['username'],params['password'],params['remotePath'],params['remoteBackupPath'],params['backMaxNum'],params['localPath']))
            connection.commit()
            return self.success(None)
        except Exception as e:
            return self.error(str(e))
        finally:
            cursor.close()
            self.lock.release()
     # !2
    # 获取任务列表
    def getJobList(self):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            cursor.execute("select * from jobList")
            return self.success(row_to_dict(cursor.fetchall()))
        except Exception as e:
            return self.error(str(e))
        finally:
            cursor.close()
            self.lock.release()
        
     # 获取任务列表 by IDs
    def getJobListByIds(self , jobIds):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            placeholders = ', '.join(['?'] * len(jobIds))
            query = f"select * from jobList where id in ({placeholders})"
            cursor.execute(query,(jobIds))
            connection.commit()
            return self.success(row_to_dict(cursor.fetchall()))
        except Exception as e:
            print(f"获取任务列表 by IDs时出现错误: {e}")
            return self.error(str(e))
        finally:
            cursor.close()
            self.lock.release()

    # 获取任务列表 by status
    def getJobListByStatus(self , status):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            cursor.execute("select * from jobList where status=?",(status))
            return self.success(row_to_dict(cursor.fetchall()))
        except Exception as e:
            return self.error(str(e))
        finally:
            cursor.close()
            self.lock.release()
    # 获取任务列表 by status
    def getJobListByStatusList(self , statusList):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            # 动态生成占位符
            placeholders = ', '.join(['?'] * len(statusList))
            query = f"select * from jobList where status in ({placeholders})"
            cursor.execute(query, statusList)
            return self.success(row_to_dict(cursor.fetchall()))
        except Exception as e:
            return self.error(str(e))
        finally:
            cursor.close()
            self.lock.release()
    # 添加任务
    def addJob(self , params):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            cursor.execute(
                "insert into jobList (name,dir,buildCommond,otherCommond,host,port,username,password,remotePath,remoteBackupPath,backMaxNum,localPath,remoteCommand) values (?,?,?,?,?,?,?,?,?,?,?,?,?)"
                ,(params['name'],params['dir'],params['buildCommond'],params['otherCommond'],params['host'],params['port'],params['username'],params['password'],params['remotePath'],params['remoteBackupPath'],params['backMaxNum'],params['localPath'],params['remoteCommand']))
            connection.commit()
            return self.success(None)
        except Exception as e:
            return self.error(str(e))
        finally:
            cursor.close()
            self.lock.release()
    # 删除任务
    def delJob(self , jobId):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            id = str(jobId)
            cursor.execute("delete from jobList where id=?",(id))
            connection.commit()
            return self.success(None)
        except Exception as e:
            return self.error(str(e))
        finally:
            cursor.close()
            self.lock.release()
    # 修改任务
    def updateJob(self , params):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            cursor.execute(
                "update jobList set name=?,dir=?,buildCommond=?,otherCommond=?,host=?,port=?,username=?,password=?,remotePath=?,remoteBackupPath=?,backMaxNum=?,localPath=?,remoteCommand=? where id=?"
                ,(params['name'],params['dir'],params['buildCommond'],params['otherCommond'],params['host'],params['port'],params['username'],params['password'],params['remotePath'],params['remoteBackupPath'],params['backMaxNum'],params['localPath'],params['remoteCommand'],params['id']))
            connection.commit()
            return self.success(None)
        except Exception as e:
            return self.error(str(e))
        finally:
            cursor.close()
            self.lock.release()

    # 修改状态
    # 0 平常状态 1 待执行 2 执行中等待中 3 执行中 4 执行完成
    def updateJobStatus(self , params):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            cursor.execute("update jobList set status=? where id=?",(params['status'],params['id']))
            connection.commit()
            return self.success(None)
        except Exception as e:
            return self.error(str(e))
        finally:
            cursor.close()
            self.lock.release()
    
    # 0 平常状态 1 待执行 2 执行中等待中 3 执行中 4 执行完成
    def updateJobStatusByIds(self , jobIds ,status):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            # 动态生成占位符
            # print(jobIds, status)
            placeholders = ', '.join(['?'] * len(jobIds))
            query = f"update jobList set status=? where id in ({placeholders})"
            # 组合参数
            params = (status,) + tuple(jobIds)
            cursor.execute(query, params)
            connection.commit()
            return self.success("None")
        except Exception as e:
            print(f"修改状态时出现错误: {e}")
            return self.error(str(e))
        finally:
            cursor.close()
            self.lock.release()
    # !3
    # 获取任务日志
    def getJobLog(self):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            cursor.execute("select * from jobLog left join jobList on jobLog.jobId=jobList.id")
            return self.success(row_to_dict(cursor.fetchall()))
        except Exception as e:
            return self.error(str(e))
        finally:
            cursor.close()
            self.lock.release()
    # 添加任务日志 0 失败 1 成功
    def addJobLog(self , params):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            time = (datetime.datetime.now(datetime.timezone.utc) + datetime.timedelta(hours=8)).strftime('%Y-%m-%d %H:%M:%S')
            cursor.execute("insert into jobLog (jobId,log,createAt) values (?,?,?)",(params['jobId'],params['log'],time))
            connection.commit()
            return self.success(None)
        except Exception as e:
            print(f"添加任务日志时出现错误: {e}")
            return self.error(str(e))
        finally:
            cursor.close()
            self.lock.release()
    
    # 清空日志表的数据
    def clearJobLog(self):
        self.lock.acquire(True)
        cursor = connection.cursor()
        try:
            cursor.execute("delete from jobLog")
            connection.commit()
            return self.success(None)
        except Exception as e:
            return self.error(str(e))
        finally:
            cursor.close()
            self.lock.release()
    